<?php
namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class StatisticGame extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'statisticGame';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '小游戏每日统计';

    protected $manageCon = 'mysql';

    protected $apiCon = 'api';
    protected $gameLog_table = 'game_log';
    protected $gameUserLog_table = 'game_user_log';
    protected $roomOpenTime_table = 'room_open_time';

    protected $statisticCon = 'statistic';
    protected $gameDayStatistics_table = 'game_day_statistics';
    protected $gameRoomDayStatistics_table = 'game_room_day_statistics';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        set_time_limit(0);

        $yesterdayDate = date('Y-m-d', strtotime("-1 days"));
        $yesterdayStart = strtotime($yesterdayDate.' 00:00:00');
        $yesterdayEnd = strtotime($yesterdayDate.' 23:59:59');

        $res1 = DB::connection($this->apiCon)->table($this->gameLog_table)
                ->selectRaw("sum(battle_duration) as second_all, count(DISTINCT room_id) as room_game_num")
                ->where('battle_start_at', '>=', $yesterdayStart)
                ->where('battle_start_at', '<=', $yesterdayEnd)
                ->first();
        $res1 = objToArray($res1);

        $res2 = DB::connection($this->apiCon)->table($this->gameLog_table)
                ->selectRaw("sum(ai_num) as ai_num, count(DISTINCT room_id) as ai_game_num, sum(ai_diamond) as ai_diamond")
                ->where('ai_num', '>', 0)
                ->where('battle_start_at', '>=', $yesterdayStart)
                ->where('battle_start_at', '<=', $yesterdayEnd)
                ->first();
        $res2 = objToArray($res2);

        $res3 = DB::connection($this->apiCon)->table($this->gameLog_table)
                ->selectRaw("sum(battle_duration) as second_all, game_type")
                ->where('battle_start_at', '>=', $yesterdayStart)
                ->where('battle_start_at', '<=', $yesterdayEnd)
                ->groupBy('game_type')
                ->get();
        $res3 = array_column(objToArray($res3), 'second_all', 'game_type');

        $res4 = DB::connection($this->apiCon)->table($this->roomOpenTime_table)
                ->selectRaw("count(DISTINCT room_id) as room_open_num")
                ->where('date', '>=', $yesterdayStart)
                ->where('date', '<=', $yesterdayEnd)
                ->first();
        $res4 = objToArray($res4);

        $log = [];
        //日期
        $log['ymd'] = date("Ymd", $yesterdayStart);
        //当日游戏总分钟数
        $log['second_all'] = empty($res1['second_all']) ? 0 : $res1['second_all'];
        //当日你说我猜总分钟数
        $log['second_2'] = empty($res3['2']) ? 0 : $res3['2'];
        //当日飞行棋中分钟数
        $log['second_3'] = empty($res3['3']) ? 0 : $res3['3'];
        //当日数字炸弹总分钟数
        $log['second_4'] = empty($res3['4']) ? 0 : $res3['4'];
        //当日总在线房间数量（包含小窝）
        $log['room_open_num'] = empty($res4['room_open_num']) ? 0 : $res4['room_open_num'];
        //游戏房间开启数量
        $log['room_game_num'] = empty($res1['room_game_num']) ? 0 : $res1['room_game_num'];
        //当日成功添加机器人数量
        $log['ai_num'] = empty($res2['ai_num']) ? 0 : $res2['ai_num'];
        //机器人的总局数
        $log['ai_game_num'] = empty($res2['ai_game_num']) ? 0 : $res2['ai_game_num'];
        //机器人的总消耗钻石
        $log['ai_diamond'] = empty($res2['ai_diamond']) ? 0 : $res2['ai_diamond'];

        //每日统计
        Db::connection($this->statisticCon)->table($this->gameDayStatistics_table)->insertGetId($log);


        $res5 = DB::connection($this->apiCon)->table($this->gameLog_table)
                ->selectRaw("count(1) as game_num, sum(ai_num) as ai_num, room_id")
                ->where('battle_start_at', '>=', $yesterdayStart)
                ->where('battle_start_at', '<=', $yesterdayEnd)
                ->groupBy('room_id')
                ->get();
        $res5 = objToArray($res5);

        $res6 = DB::connection($this->apiCon)->table($this->gameUserLog_table)
                ->selectRaw("count(DISTINCT user_id) as user_num, room_id")
                ->where('created_at', '>=', date('Y-m-d H:i:s', $yesterdayStart))
                ->where('created_at', '<=', date('Y-m-d H:i:s', $yesterdayEnd))
                ->groupBy('room_id')
                ->get();
        $res6 = array_column(objToArray($res6), 'user_num', 'room_id');

        //每日房间统计
        foreach($res5 as $val) {
            $log = [];
            //日期
            $log['ymd'] = date("Ymd", $yesterdayStart);
            //房间ID
            $log['room_id'] = $val['room_id'];
            //每天游戏局数
            $log['game_num'] = $val['game_num'];
            //每天参与真实人数
            $log['user_num'] = empty($res6[$val['room_id']]) ? 0 : $res6[$val['room_id']];
            //机器人数量
            $log['ai_num'] = $val['ai_num'];

            Db::connection($this->statisticCon)->table($this->gameRoomDayStatistics_table)->insertGetId($log);
        }
    }



}
